/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package org.blog.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
//import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.ResultSet;
import org.blog.idao.IArticleDAO;
import org.blog.model.Article;
import org.blog.model.Author;
/**
 *
 * @author danang
 */
public class ArticleDAO implements IArticleDAO {

    private Connection connection;

    public ArticleDAO(Connection connection) {
        this.connection = connection;
    }

    public int insert(Article article) throws SQLException {
        String sql = "insert into article(title,content,author_id) values(?,?,?)";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setString(1, article.getTitle());
        ps.setString(2, article.getContent());
        ps.setInt(3, article.getAuthor().getId());

        return ps.executeUpdate();
    }

    public int update(int id, Article articleUpdate) throws SQLException {
        String sql = "update article set title=?, content=?, author_id=? "
                + "where article_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, articleUpdate.getTitle());
        ps.setString(2, articleUpdate.getContent());
        ps.setInt(3, articleUpdate.getAuthor().getId());
        ps.setInt(4, id);

        return ps.executeUpdate();
    }

    public int delete(int id) throws SQLException {
        String sql = "delete from article where article_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setInt(1, id);

        return ps.executeUpdate();
    }

    public Article getById(int id) throws SQLException {
        String sql = "a.article_id, a.title, a.content, a.author_id "
                + "b.author_id, b.username, b.password, b.real_name, b.about "
                + "inner join author b on(a.author_id = b.author_id) "
                + "where a.article_id=?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        Article article = null;
        while (rs.next()) {
            Author author = new Author();
            author.setId(rs.getInt("author_id"));
            author.setUsername(rs.getString("username"));
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));

            article = new Article();
            article.setId(id);
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setAuthor(author);
        }
        return article;
    }

    public List<Article> getAll() throws SQLException {
        String sql = "a.article_id, a.title, a.content, a.author_id "
                + "b.author_id, b.username, b.password, b.real_name, b.about "
                + "inner join author b on(a.author_id = b.author_id)";
        Statement st = connection.createStatement();
        ResultSet rs = st.executeQuery(sql);

        List<Article> listArticle = new ArrayList<Article>();

        while(rs.next()){
            Author author = new Author();
            author.setId(rs.getInt("author_id"));
            author.setUsername(rs.getString("username"));
            author.setPassword(rs.getString("password"));
            author.setRealName(rs.getString("real_name"));
            author.setAbout(rs.getString("about"));

            Article article = new Article();
            article.setId(rs.getInt("article_id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));
            article.setAuthor(author);

            listArticle.add(article);
        }
        return listArticle;
    }

    public List<Article> getByTitleLike(String title) throws SQLException {
        String sql = "select * from article where title like ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, "%" + title + "%");
        ResultSet rs = ps.executeQuery();
        List<Article> listArticle = new ArrayList<Article>();

        while(rs.next()){
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));

            AuthorDAO authorDAO = new AuthorDAO(connection);

            article.setAuthor(authorDAO.getById(rs.getInt("author_id")));

            listArticle.add(article);
        }
        return listArticle;
    }

    public List<Article> getByContentLike(String content) throws SQLException {
        String sql = "select * from article where content like ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps = this.connection.prepareStatement(sql);
        ps.setString(1, "%" + content + "%");
        ResultSet rs = ps.executeQuery();
        List<Article> listArticle = new ArrayList<Article>();

        while(rs.next()){
            Article article = new Article();
            article.setId(rs.getInt("id"));
            article.setTitle(rs.getString("title"));
            article.setContent(rs.getString("content"));

            AuthorDAO authorDAO = new AuthorDAO(connection);

            article.setAuthor(authorDAO.getById(rs.getInt("author_id")));

            listArticle.add(article);
        }
        return listArticle;
    }
}
